package com.xhhospital.dao;

import com.xhhospital.entity.Brxx;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class BrxxDao {
    //完成数据库连接
    public static Connection getConn() {
        Connection conn = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/library", "root", "200253");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }

    //显示数据库记录
    public static List<Brxx> showAllBrxx() {
        List<Brxx> list = new ArrayList<Brxx>();
        try {
            Connection conn = BrxxDao.getConn();
            Statement st = conn.createStatement();
            String sql = "select jzkh,blbh,brxm from hxyy where z=0";
            ResultSet rs = st.executeQuery(sql);
            if (rs != null) {
                while (rs.next()) {
                    Brxx brxx = new Brxx();
                    brxx.setJzkh(rs.getInt(1));
                    brxx.setBlbh(rs.getString(2));
                    brxx.setBrxm(rs.getString(3));
                    list.add(brxx);
                }
            }
            rs.close();
            st.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    //数据保存//添加
    public static int addBrxx(Brxx brxx) {
        Connection conn = null;
        int stauts = 0;
        try {
            conn = BrxxDao.getConn();//获取数据库连接对象
            String sql = "insert into hxyy(blbh,brxm,brxb,ysbh,ysxm,yszc,ypmc,ypgg,ypsl,zdjg,zlff,jzks,zdsj) values (?,?,?,?,?,?,?,?,?,?,?,?,?)";
            PreparedStatement ps = conn.prepareStatement(sql);
            //赋值
//            ps.setInt(1, brxx.getJzkh());
            ps.setString(1, brxx.getBlbh());
            ps.setString(2, brxx.getBrxm());
            ps.setString(3, brxx.getBrxb());
            ps.setString(4, brxx.getYsbh());
            ps.setString(5, brxx.getYsxm());
            ps.setString(6, brxx.getYszc());
            ps.setString(7, brxx.getYpmc());
            ps.setString(8, brxx.getYpgg());
            ps.setString(9, brxx.getYpsl());
            ps.setString(10, brxx.getZdjg());
            ps.setString(11, brxx.getZlff());
            ps.setString(12, brxx.getJzks());
            ps.setString(13, brxx.getZdsj());
            // ps.setString(3,dorm.getTp());
            stauts = ps.executeUpdate();
            //fe
            //eg
            //ee
            ps.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return stauts;
    }

    //根据宿舍房间号查询
    public static List<Brxx> getBrxxById(String blbh) {
        Connection conn = null;
        List<Brxx> list = new ArrayList<Brxx>();
        try {
            conn = BrxxDao.getConn();
            String sql = "select jzkh,blbh,brxm from hxyy where z=0 and blbh like ?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, "%" + blbh + "%");
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                int jzkh = rs.getInt(1);
                String blbh1 = rs.getNString(2);
                String brxm = rs.getString(3);
                Brxx brxx = new Brxx();
                brxx.setJzkh(jzkh);
                brxx.setBlbh(blbh1);
                brxx.setBrxm(brxm);
                list.add(brxx);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }
    //修改
    public static List<Brxx> getBrxxByBh(int jzkh){
        List<Brxx> list = new ArrayList<Brxx>();
        try{
            Connection conn=BrxxDao.getConn();
            Statement st=conn.createStatement();
            String sql="select jzkh,brxm,brxb,jzks,ysxm,lxfs from hxyy where z=0 and jzkh="+jzkh;
            ResultSet rs=st.executeQuery(sql);
            if(rs!=null){
                while (rs.next()){
                    int jzkh1 = rs.getInt(1);
                    String brxm = rs.getNString(2);
                    String brxb = rs.getString(3);
                    String jzks = rs.getString(4);
                    String ysxm = rs.getString(5);
                    String lxfs = rs.getString(6);
                    Brxx brxx = new Brxx();
                    brxx.setJzkh(jzkh1);
                    brxx.setBrxm(brxm);
                    brxx.setBrxb(brxb);
                    brxx.setJzks(jzks);
                    brxx.setYsxm(ysxm);
                    brxx.setLxfs(lxfs);
                    list.add(brxx);
                }
            }
            rs.close();
            st.close();
            conn.close();
        }catch(Exception e){
            e.printStackTrace();
        }
        return list;
    }
    public static int updateBrxx(Brxx brxx){
        int sta=0;
        try{
            Connection conn=BrxxDao.getConn();//获取数据库连接
            String sql="update hxyy set brxm=?,brxb=?,jzks=?,ysxm=?,lxfs=? where jzkh=? and z=0";//修改
            PreparedStatement ps=conn.prepareStatement(sql);
            ps.setString(1,brxx.getBrxm());
            ps.setString(2,brxx.getBrxb());
            ps.setString(3,brxx.getJzks());
            ps.setString(4,brxx.getYsxm());
            ps.setString(5,brxx.getLxfs());
            ps.setInt(6,brxx.getJzkh());
                sta=ps.executeUpdate();
            ps.close();
            conn.close();
        }catch(Exception e){
            e.printStackTrace();
        }
        return sta;
    }
    //删除指定记录，根据编号
    public static int delBrxxByBh(int jzkh){
        int sta=0;
        try{
            Connection con=BrxxDao.getConn();
            Statement st=con.createStatement();
            String sql="update hxyy set z=1 where jzkh="+jzkh;
            sta=st.executeUpdate(sql);
            st.close();
            con.close();
        }catch(Exception e){
            e.printStackTrace();
        }
        return sta;
    }
}
